{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## import useful packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import datetime\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set_style(\"white\")\n",
    "from matplotlib_venn import venn2\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "from pydqc.data_compare import distribution_compare_pretty"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## assign values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# the data table (pandas DataFrame)\n",
    "table1 =\n",
    "table2 =\n",
    "print(\"table1 size: \" + str(table1.shape))\n",
    "print(\"table2 size: \" + str(table2.shape))\n",
    "\n",
    "# global values\n",
    "TABLE1_DARK = \"#4BACC6\"\n",
    "TABLE1_LIGHT = \"#DAEEF3\"\n",
    "TABLE2_DARK = \"#F79646\"\n",
    "TABLE2_LIGHT = \"#FDE9D9\"\n",
    "\n",
    "# get date of today\n",
    "snapshot_date_now = str(datetime.datetime.now().date())\n",
    "print(\"date of today: \" + snapshot_date_now)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## error columns\n",
    "\n",
    "**decktypeid:** exclude<br>**hashottuborspa:** exclude<br>**poolcnt:** exclude<br>**pooltypeid10:** exclude<br>**pooltypeid2:** exclude<br>**pooltypeid7:** exclude<br>**storytypeid:** exclude<br>**fireplaceflag:** exclude<br>**taxdelinquencyflag:** exclude<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## parcelid (type: key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"parcelid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw venn graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 5))\n",
    "venn2([set_df1_col, set_df2_col], set_labels=[\"table1\", \"table2\"],set_colors=(\"#4BACC6\", \"#F79646\"), alpha=0.8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## airconditioningtypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"airconditioningtypeid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## architecturalstyletypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"architecturalstyletypeid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## basementsqft (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"basementsqft\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## bathroomcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"bathroomcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## bedroomcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"bedroomcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## buildingclasstypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"buildingclasstypeid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## buildingqualitytypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"buildingqualitytypeid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## calculatedbathnbr (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"calculatedbathnbr\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedfloor1squarefeet (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"finishedfloor1squarefeet\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## calculatedfinishedsquarefeet (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"calculatedfinishedsquarefeet\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet12 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet12\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet13 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet13\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet15 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet15\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet50 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet50\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet6 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet6\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fips (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"fips\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fireplacecnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"fireplacecnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fullbathcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"fullbathcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## garagecarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"garagecarcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## garagetotalsqft (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"garagetotalsqft\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## heatingorsystemtypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"heatingorsystemtypeid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## latitude (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"latitude\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## longitude (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"longitude\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## lotsizesquarefeet (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"lotsizesquarefeet\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## poolsizesum (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"poolsizesum\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## propertycountylandusecode (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"propertycountylandusecode\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## propertylandusetypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"propertylandusetypeid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## propertyzoningdesc (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"propertyzoningdesc\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## rawcensustractandblock (type: key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"rawcensustractandblock\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw venn graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 5))\n",
    "venn2([set_df1_col, set_df2_col], set_labels=[\"table1\", \"table2\"],set_colors=(\"#4BACC6\", \"#F79646\"), alpha=0.8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidcity (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"regionidcity\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidcounty (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"regionidcounty\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidneighborhood (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"regionidneighborhood\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidzip (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"regionidzip\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## roomcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"roomcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## threequarterbathnbr (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"threequarterbathnbr\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## typeconstructiontypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"typeconstructiontypeid\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## unitcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"unitcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## yardbuildingsqft17 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"yardbuildingsqft17\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## yardbuildingsqft26 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"yardbuildingsqft26\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## yearbuilt (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"yearbuilt\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## numberofstories (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"numberofstories\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## structuretaxvaluedollarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"structuretaxvaluedollarcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## taxvaluedollarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"taxvaluedollarcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## assessmentyear (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"assessmentyear\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df1 = pd.DataFrame(df1[col].value_counts())\n",
    "value_counts_df1.columns = [\"count_1\"]\n",
    "value_counts_df1[col] = value_counts_df1.index.values\n",
    "value_counts_df1 = value_counts_df1.reset_index(drop=True)[[col, \"count_1\"]]\n",
    "value_counts_df1 = value_counts_df1.sort_values(by=\"count_1\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df2 = pd.DataFrame(df2[col].value_counts())\n",
    "value_counts_df2.columns = [\"count_2\"]\n",
    "value_counts_df2[col] = value_counts_df2.index.values\n",
    "value_counts_df2 = value_counts_df2.reset_index(drop=True)[[col, \"count_2\"]]\n",
    "value_counts_df2 = value_counts_df2.sort_values(by=\"count_2\", ascending=False).head(10)\n",
    "\n",
    "value_counts_df = value_counts_df1.merge(value_counts_df2, on=col, how=\"outer\").fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_counts_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## landtaxvaluedollarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"landtaxvaluedollarcnt\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## taxamount (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"taxamount\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## taxdelinquencyyear (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"taxdelinquencyyear\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))\n",
    "\n",
    "# min value\n",
    "value_min1 = df1[col].min()\n",
    "value_min2 = df2[col].min()\n",
    "print(\"table1 min: \" + str(value_min1) + \"; table2 min: \" + str(value_min2))\n",
    "\n",
    "# mean value\n",
    "value_mean1 = df1[col].mean()\n",
    "value_mean2 = df2[col].mean()\n",
    "print(\"table1 mean: \" + str(value_mean1) + \"; table2 mean: \" + str(value_mean2))\n",
    "\n",
    "# median value\n",
    "value_median1 = df1[col].median()\n",
    "value_median2 = df2[col].median()\n",
    "print(\"table1 median: \" + str(value_median1) + \"; table2 median: \" + str(value_median2))\n",
    "\n",
    "# max value\n",
    "value_max1 = df1[col].max()\n",
    "value_max2 = df2[col].max()\n",
    "print(\"table1 max: \" + str(value_max1) + \"; table2 max: \" + str(value_max2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "value_dropna_df1 = df1[col].dropna().values\n",
    "value_dropna_df2 = df2[col].dropna().values\n",
    "plt.figure(figsize=(10, 5))\n",
    "sns.distplot(value_dropna_df1, color=\"#4BACC6\", norm_hist=True, hist=False, label=\"table1\")\n",
    "sns.distplot(value_dropna_df2, color=\"#F79646\", norm_hist=True, hist=False, label=\"table2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# you can also use the build-in draw function\n",
    "distribution_compare_pretty(df1, df2, col, figsize=None, date_flag=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## censustractandblock (type: key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col = \"censustractandblock\"\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### basic comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sample values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# num_uni\n",
    "num_uni1 = df1[col].dropna().nunique()\n",
    "num_uni2 = df2[col].dropna().nunique()\n",
    "\n",
    "print(\"table1 num_uni out of \" + str(df1[col].dropna().shape[0]) + \": \" + str(num_uni1))\n",
    "print(\"table2 num_uni out of \" + str(df2[col].dropna().shape[0]) + \": \" + str(num_uni2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw venn graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 5))\n",
    "venn2([set_df1_col, set_df2_col], set_labels=[\"table1\", \"table2\"],set_colors=(\"#4BACC6\", \"#F79646\"), alpha=0.8)"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 2
}
